package com.example.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: wlai
 * Date: 12/11/12
 * Time: 7:17 PM
 * To change this template use File | Settings | File Templates.
 */
public class DbHelper extends SQLiteOpenHelper {
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "demo.db";

    private static final String SQL_CREATE_CATEGORY_TABLE = "create table " + CategoryEntry.TABLE_NAME + " ( " +
            CategoryEntry.COLUMN_NAME_CATEGORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + CategoryEntry.COLUMN_NAME_CATEGORY_NAME + " varchar(40) not null)";
    private static final String SQL_CREATE_RECORD_TABLE = "create table " + RecordEntry.TABLE_NAME + " ( " +
            RecordEntry.COLUMN_NAME_RECORD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + RecordEntry.COLUMN_NAME_RECORD_MONEY + " number not null,"
            + RecordEntry.COLUMN_NAME_RECORD_CATEGORY + " INTEGER not null," +
            RecordEntry.COLUMN_NAME_RECORD_TIME + " date not null)";


    private static final String SQL_DELETE_CATEGORY_TABLE =
            "DROP TABLE IF EXISTS " + CategoryEntry.TABLE_NAME;

    private static final String SQL_DELETE_RECORD_TABLE =
            "DROP TABLE IF EXISTS " + RecordEntry.TABLE_NAME;

    public long addCategory(String name) {
        ContentValues values = new ContentValues();
        values.put(CategoryEntry.COLUMN_NAME_CATEGORY_NAME, name);
        SQLiteDatabase db = this.getWritableDatabase();
        long newRowId;
        newRowId = db.insert(
                CategoryEntry.TABLE_NAME,
                CategoryEntry.COLUMN_NAME_CATEGORY_NAME,
                values);

        return newRowId;

    }


    public Cursor listCategory() {
        SQLiteDatabase db = this.getReadableDatabase();

// Define a projection that specifies which columns from the database
// you will actually use after this query.
        String[] projection = {
                CategoryEntry.COLUMN_NAME_CATEGORY_ID,
                CategoryEntry.COLUMN_NAME_CATEGORY_NAME
        };

// How you want the results sorted in the resulting Cursor
        String sortOrder = null;

        Cursor c = db.query(
                CategoryEntry.TABLE_NAME,  // The table to query
                projection,                               // The columns to return
                null,                                // The columns for the WHERE clause
                null,                            // The values for the WHERE clause
                null,                                     // don't group the rows
                null,                                     // don't filter by row groups
                sortOrder                                 // The sort order
        );
        return c;

    }

    public long addRecord(RecordEntry record) {
        ContentValues values = new ContentValues();
        values.put(RecordEntry.COLUMN_NAME_RECORD_MONEY, record.getMoney());
        values.put(RecordEntry.COLUMN_NAME_RECORD_CATEGORY, record.getCategoryId());
        values.put(RecordEntry.COLUMN_NAME_RECORD_TIME, record.getRecordTime().getTime());
        SQLiteDatabase db = this.getWritableDatabase();
        long newRowId;
        newRowId = db.insert(
                RecordEntry.TABLE_NAME,
                null,
                values);

        return newRowId;
    }

    public Cursor listRecord() {
        SQLiteDatabase db = this.getReadableDatabase();

// Define a projection that specifies which columns from the database
// you will actually use after this query.
        String[] projection = {
                RecordEntry.COLUMN_NAME_RECORD_ID,
                RecordEntry.COLUMN_NAME_RECORD_CATEGORY, RecordEntry.COLUMN_NAME_RECORD_MONEY, RecordEntry.COLUMN_NAME_RECORD_TIME
        };

// How you want the results sorted in the resulting Cursor
        String sortOrder = null;

        Cursor c = db.query(
                CategoryEntry.TABLE_NAME,  // The table to query
                projection,                               // The columns to return
                null,                                // The columns for the WHERE clause
                null,                            // The values for the WHERE clause
                null,                                     // don't group the rows
                null,                                     // don't filter by row groups
                sortOrder                                 // The sort order
        );
        return c;

    }

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(SQL_CREATE_CATEGORY_TABLE);
        sqLiteDatabase.execSQL(SQL_CREATE_RECORD_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {
        sqLiteDatabase.execSQL(SQL_DELETE_CATEGORY_TABLE);
        sqLiteDatabase.execSQL(SQL_DELETE_RECORD_TABLE);
        onCreate(sqLiteDatabase);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }


}
